package com.raspositos.guitarfrik.android;

import java.util.SortedSet;
import java.util.TreeSet;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TrackSectionDAO extends SQLiteOpenHelper {

    public TrackSectionDAO(Context context) {
    	 super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	private static final String DATABASE_NAME = "track_sections.db";
    private static final int DATABASE_VERSION = 2;
    private static final String SECTIONS_TABLE_NAME = "sections";

	

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + SECTIONS_TABLE_NAME + " ("
                    + TrackSection._ID + " INTEGER PRIMARY KEY,"
                    + TrackSection.TRACK_ID + " INTEGER,"
                    + TrackSection.NAME + " TEXT,"
                    + TrackSection.INDEX + " TEXT,"
                    + TrackSection.OFFSET + " INTEGER,"
                    + TrackSection.DURATION + " INTEGER"
                    + ");");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//            db.execSQL("DROP TABLE IF EXISTS notes");
//            onCreate(db);
        }
        
        public long insert(TrackSection ts){
        	ContentValues values = fillContentValues(ts);
        	SQLiteDatabase db = getWritableDatabase();
        	return db.insert(SECTIONS_TABLE_NAME, "",values);
        }

        public void delete(TrackSection ts){
        	SQLiteDatabase db = getWritableDatabase();
        	db.delete(SECTIONS_TABLE_NAME, TrackSection._ID + " =  ?", new String[]{"" + ts.getId()});
        }

		public SortedSet<TrackSection> loadAll(TrackDescriptor td) {
			SortedSet<TrackSection> sections = new  TreeSet<TrackSection>();
	        SQLiteDatabase db = getReadableDatabase();
	        Cursor c = null;
	        try{
		        c = db.query(SECTIONS_TABLE_NAME, 
		        		new String[]{TrackSection._ID, TrackSection.TRACK_ID, TrackSection.NAME,
		        		TrackSection.INDEX, TrackSection.OFFSET, TrackSection.DURATION}, 
		        		TrackSection.TRACK_ID + " =  ?", new String[]{"" + td.getId()}, "", "", "");
		        c.moveToFirst();
		        while (!c.isAfterLast()){
		        	TrackSection ts = new TrackSection(td.getId(),
		        			c.getInt(c.getColumnIndex(TrackSection.OFFSET)),
		        			c.getInt(c.getColumnIndex(TrackSection.DURATION)),
		        			c.getString(c.getColumnIndex(TrackSection.NAME)));
		        	ts.setId(c.getLong(c.getColumnIndex(TrackSection._ID)));
		        	ts.setIndex(c.getString(c.getColumnIndex(TrackSection.INDEX)));
		        	sections.add(ts);
		        	c.moveToNext();
		        }
	        } catch (SQLException e){
	    		Log.e("GuitarFrik", "Unexpected error while fetching track sections.", e);
	        } finally {
	        	if (c != null )
	        		c.close();
	        }
			return sections;
		}

		public void update(TrackSection ts) {
			ContentValues values = fillContentValues(ts);
        	SQLiteDatabase db = getWritableDatabase();
        	db.update(SECTIONS_TABLE_NAME, values, TrackSection._ID + " = ?", new String[]{""+ ts.getId()});
		}

		private ContentValues fillContentValues(TrackSection ts) {
			ContentValues values = new ContentValues();
        	values.put(TrackSection.TRACK_ID, ts.getTrackId());
        	values.put(TrackSection.NAME, ts.getName());
        	values.put(TrackSection.INDEX, ts.getIndex());
        	values.put(TrackSection.OFFSET, ts.getOffset());
        	values.put(TrackSection.DURATION, ts.getDuration());
			return values;
		}
		
}
